﻿using System;
using System.Data;
using System.Data.Common;
using MediaBrowserSL.BOL;
using MediaBrowserSL.DAL.Contexts;
using MediaBrowserSL.IDAL;

namespace MediaBrowserSL.DAL
{
    public class SqlUserRepository : IUserRepository
    {
        /// <summary>
        /// Exist User
        /// </summary>
        /// <param name="Login"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public User ExistUser(string Login)
        {
            String prock = "select * from UserMedia where Login = @login";
            User user = null;
            try
            {
                using (DbConnection db = SqlDataContext.DataContext)
                {
                    using (DbCommand cmd = db.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = prock;
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@login", Login, DbType.String));

                        using (DbDataReader reader = cmd.ExecuteReader())
                        {

                            if (reader.HasRows)
                            {
                                reader.Read();
                                user = new User()
                                {
                                    Id = (reader["Id"].ToString().Length > 0) ? reader.GetInt32(reader.GetOrdinal("Id")) : 0,
                                    FirstName = (reader["FirstName"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("FirstName")) : String.Empty,
                                    LastName = (reader["LastName"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("LastName")) : String.Empty,
                                    Login = (reader["Login"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("Login")) : String.Empty,
                                    Password = (reader["Password"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("Password")) : String.Empty,
                                    IsConnected = (reader["IsConnected"].ToString().Length > 0) ? reader.GetBoolean(reader.GetOrdinal("IsConnected")) : false
                                };
                            }
                        }
                    }
                }
            }
            catch (DbException sqlEx)
            {
                throw new SqlException(prock, "", sqlEx);
            }
            return user;
        }

        /// <summary>
        /// Create new User
        /// </summary>
        /// <param name="firstName"></param>
        /// <param name="lastName"></param>
        /// <param name="Login"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public int CreateUser(string firstName, string lastName, string Login, string password)
        {
            String prock = "INSERT INTO UserMedia VALUES(@firstName,@lastName,@Login,@password,@isConnected)";
            prock += " SELECT @@Identity ";

            int idUser = 1;
            try
            {
                using (DbConnection db = SqlDataContext.DataContext)
                {
                    using (DbCommand cmd = db.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = prock;
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@firstName", firstName, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@lastName", lastName, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@Login", Login, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@password", password, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@isConnected", false, DbType.Boolean));
                        idUser = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
            }
            catch (DbException sqlEx)
            {
                throw new SqlException(prock, "", sqlEx);
            }
            return idUser;
        }

        /// <summary>
        /// Update Statut User
        /// </summary>
        /// <param name="u"></param>
        public void UpdateStatut(User u)
        {
            String prock = "UPDATE UserMedia SET ";
            prock += "IsConnected = @isConnected ";
            prock += "where Id = " + u.Id;

            try
            {
                using (DbConnection db = SqlDataContext.DataContext)
                {
                    using (DbCommand cmd = db.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = prock;
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@isConnected", u.IsConnected, DbType.Boolean));

                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (DbException sqlEx)
            {
                throw new SqlException(prock, "", sqlEx);
            }
        }

        /// <summary>
        /// Exist Login
        /// </summary>
        /// <param name="Login"></param>
        /// <returns></returns>
        public bool ExistLogin(string Login)
        {
            String prock = "select 1 from UserMedia where Login = @login ";

            bool exist = false;
            try
            {
                using (DbConnection db = SqlDataContext.DataContext)
                {
                    using (DbCommand cmd = db.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = prock;
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@login", Login, DbType.String));
                        var r = cmd.ExecuteScalar();
                        if (r != null) { exist = true; }
                    }
                }
            }
            catch (DbException sqlEx)
            {
                throw new SqlException(prock, "", sqlEx);
            }
            return exist;
        }
    }
}
